多表操作

一对多/多对一查询方式：

1. 子查询
2. 联表查询

要求：通过学生查询老师和学生的信息。

1. 准备工作

1. SQL语句

   CREATE TABLE `teacher` ( `id` INT ( 10 ) NOT NULL, `name` VARCHAR ( 30 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
   INSERT INTO teacher ( `id`, `name` )
   VALUES
       ( 1, '张三老师' );
   CREATE TABLE `student` (
       `id` INT ( 10 ) NOT NULL,
       `name` VARCHAR ( 30 ) DEFAULT NULL,
       `tid` INT ( 10 ) DEFAULT NULL,
       PRIMARY KEY ( `id` ),
       KEY `fktid` ( `tid` ),
       CONSTRAINT `fktid` FOREIGN KEY ( `tid` ) REFERENCES `teacher` ( `id` ) 
   ) ENGINE = INNODB DEFAULT CHARSET = utf8;
   INSERT INTO `student` ( `id`, `name`, `tid` )
   VALUES
       ( '1', '小明', '1' );
   INSERT INTO `student` ( `id`, `name`, `tid` )
   VALUES
       ( '2', '小红', '1' );
   INSERT INTO `student` ( `id`, `name`, `tid` )
   VALUES
       ( '3', '小张', '1' );
   INSERT INTO `student` ( `id`, `name`, `tid` )
   VALUES
       ( '4', '小李', '1' );
   INSERT INTO `student` ( `id`, `name`, `tid` )
   VALUES
       ( '5', '小王', '1' );
   

   

1.1. 准备工作

1. pojo

   package org.gs.pojo;
   
   import lombok.AllArgsConstructor;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   
   /**
    * @author admin
    * @date 2021/9/11 4:34 下午
    */
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class Student {
       private int id;
       private String name;
       private Teacher teacher;    // 多对一的查询加上
   }
   

   package org.gs.pojo;
   
   import lombok.AllArgsConstructor;
   import lombok.Data;
   import lombok.NoArgsConstructor;
   
   /**
    * @author admin
    * @date 2021/9/11 4:33 下午
    */
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class Teacher {
       private int id;
       private String name;
         private List<Student> students;    // 一对多的查询加上
   }
   

2. 多对一查询

1. 子查询（按照查询嵌套处理）

   思路：

   1. 查询所有的学生信息
   2. 根据查询出来的学生id，寻找对应的老师

   <resultMap id="StudentAndTeacher" type="Student">
     <!--能自动映射的可以不配置-->
     <!--javaType指定子查询映射的pojo对象-->
     <!--property需要封装的对象变量名-->
     <!--column子查询调用的前面查询的参数的字段名-->
     <!--下面的select字段只能指定当前mapper的某个select块-->
     <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
   </resultMap>
   
   <select id="getStudents" resultMap="StudentAndTeacher">
     select *
     from student
   </select>
   
   <select id="getTeacher" resultType="Teacher">
     select *
     from teacher
     where id = #{id}
   </select>
   

2. 联表查询（按照结果嵌套处理）

   思路：一次性查出所有需要的结果，然后一一映射。

   <resultMap id="StudentAndTeacher2" type="Student">
     <result property="id" column="sid"/>
     <result property="name" column="sname"/>
     <association property="teacher" javaType="teacher">
       <result property="id" column="tid"/>
       <result property="name" column="tname"/>
     </association>
   </resultMap>
   
   <select id="getStudents2" resultMap="StudentAndTeacher2">
     select s.id sid, s.name sname, t.id tid, t.name tname
     from student s,
     teacher t
     where s.tid = t.id
   </select>
   

3. 一对多查询

1. 子查询（按照查询嵌套处理）

   <resultMap id="TeacherAndStudents" type="Teacher">
     <result property="id" column="id"/>
     <collection property="students" column="id" select="getStudentsByTeacherId" ofType="Student"/>
   </resultMap>
   
   <select id="getTeahcer" resultMap="TeacherAndStudents">
     select *
     from teacher
     where id = #{id}
   </select>
   
   <select id="getStudentsByTeacherId" resultType="Student">
     select *
     from student
     where tid = #{id}
   </select>
   

2. 联表查询（按照结果嵌套处理）

   <resultMap id="TeacherAndStudents2" type="Teacher">
     <result property="id" column="tid"/>
     <result property="name" column="tname"/>
     <collection property="students" javaType="list" ofType="Student">
       <result property="id" column="sid"/>
       <result property="name" column="sname"/>
     </collection>
   
   </resultMap>
   
   <select id="getTeahcer2" resultMap="TeacherAndStudents2">
     select s.id sid, s.name sname, t.id tid, t.name tname
     from student s,
     teacher t
     where t.id = #{id}
     and t.id = s.tid
   </select>
   </mapper>